package com.sky.mapper;

import com.sky.entity.DataOrder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface ReportMapper {
//    List<Double> QueryAmount(Integer status);

/**查询完成订单的信息*/
    List<LocalDateTime> QueryDate(@Param("status") Integer status, @Param("begin") LocalDateTime begin, @Param("end") LocalDateTime end);

/**获取商品销售数量*/
    Integer QueryShoppingSaleCount(@Param("ids") List<Long> ids,@Param("name") String name);

    /**获取商品的名称*/
    List<String> QueryShoppcingName(List<Long> ids);

    /**根据时间获取订单的id*/
    List<Long> QueryOrderId(@Param("begin") LocalDateTime begin, @Param("end") LocalDateTime end);

    /**新用户数量*/
    Integer queryNewCount(LocalDateTime begin);

    /**用户总数量*/
    Integer queryAllCount(LocalDateTime begin);

    /**查询当天所有订单*/
    @Select("select count(id) as order_total,DATE_FORMAT(order_time,'%Y-%m-%d') as order_time from orders " +
            "where DATE_FORMAT(order_time,'%Y-%m-%d') > #{beginTime} and DATE_FORMAT(order_time,'%Y-%m-%d') < #{plusDays} " +
            "group by DATE_FORMAT(order_time,'%Y-%m-%d')")
    List<DataOrder> queryAllOrdersByDay(@Param("beginTime") LocalDateTime beginTime,@Param("plusDays") LocalDateTime plusDays);

    /**查询每日有效订单数*/
    @Select("select count(*) from orders where order_time > #{beginTime} and order_time < #{plusDays} and status = #{completed} ")
    Integer queryValidOrdersByDay(@Param("beginTime") LocalDateTime beginTime,@Param("plusDays") LocalDateTime plusDays, @Param("completed") Integer completed);

    /**查询当天订单总数*/
    @Select("select count(id) from orders where date_format(order_time,'%Y-%m-%d') between #{begin} and #{end}")
    Integer queryAllOrders(@Param("begin") LocalDate begin, @Param("end") LocalDate end);

    /**查询有效订单数*/
    @Select("select count(id) from orders where status = #{status} and date_format(order_time,'%Y-%m-%d') between #{begin} and #{end}")
    Integer queryValidOrders(@Param("begin") LocalDate begin, @Param("end") LocalDate end,@Param("status") Integer status);

    /**查询盈利金额*/
    List<DataOrder> QueryAmount(@Param("status") Integer status, @Param("begin") LocalDateTime begin, @Param("end") LocalDateTime end);

    /**获取近期的营业总额*/
    Double QueryAmountByMonth(@Param("begin") LocalDate begin, @Param("end") LocalDate end,@Param("status")Integer status);

    /**每天的顾客数量*/
    List<Integer> queryNewCountByMonth(@Param("begin") LocalDate begin, @Param("end")LocalDate end);

    /**每日有效订单数*/
    @Select("SELECT DATE_FORMAT(order_time,'%Y-%m-%d') as order_time ,COUNT(*) AS orderNum FROM orders " +
            "WHERE STATUS = #{status} and date_format(order_time,'%Y-%m-%d') between #{begin} and #{end} " +
            "GROUP BY DATE_FORMAT(order_time,'%Y-%m-%d') ")
    List<DataOrder> queryValidOrderByDay(@Param("begin") LocalDate begin, @Param("end") LocalDate end,@Param("status")Integer status);

    /**每天订单数据*/
    @Select("SELECT DATE_FORMAT(order_time,'%Y-%m-%d'),COUNT(*) AS orderTotal FROM orders " +
            "WHERE date_format(order_time,'%Y-%m-%d') between #{begin} and #{end} " +
            "GROUP BY DATE_FORMAT(order_time,'%Y-%m-%d') ")
    List<DataOrder> queryOrderDataByDay(@Param("begin") LocalDate begin, @Param("end") LocalDate end);
/**查询当天有效订单信息*/
@Select("select count(id) as order_num,DATE_FORMAT(order_time,'%Y-%m-%d') as order_time from orders " +
        "where STATUS = #{status} and date_format(order_time,'%Y-%m-%d') between #{begin} and #{end} < #{end} " +
        "group by DATE_FORMAT(order_time,'%Y-%m-%d')")
    List<DataOrder> queryValidAllOrderByDay(@Param("begin") LocalDate begin, @Param("end") LocalDate end,@Param("status")Integer status);

/**每日新增返回数量和日期*/
@Select("select count(id) as people , DATE_FORMAT(create_time,'%Y-%m-%d') as orderTime from sky_take_out.user " +
        "where date_format(create_time,'%Y-%m-%d') between #{begin} and #{end} < #{end} " +
        "group by DATE_FORMAT(create_time,'%Y-%m-%d')")
    List<DataOrder> queryNewCountByDay(@Param("begin") LocalDate begin, @Param("end") LocalDate end);
}
